﻿using EPE.DBUtility;
using EPE.IDAL;
using EPE.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace EPE.MySqlDAL
{
    public class ShopsProjectImpl : IShopsProject
    {
        public DataSet GetListByPage(string strProjectName, string companyid,string orderby, int pageIndex, int dataCount)
        {
            string strSQL;

            DataSet retDS = new DataSet();

            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " SELECT * ";
                strSQL = strSQL + " FROM tbl_ShopsProject ";
                strSQL = strSQL + " WHERE 1=1 ";
                strSQL = strSQL + " AND companyid =@companyid ";

                if (!string.IsNullOrEmpty(strProjectName))
                {
                    strSQL = strSQL + " AND projectname=@projectname ";
                }


                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("projectname", strProjectName);
                clsDbTool.ParameterAdd("companyid", companyid);
                clsDbTool.CommandText = Pagination.PagingDataView(strSQL, pageIndex, dataCount, orderby);
                clsDbTool.Open();

                retDS = clsDbTool.GetDataSet();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }
            return retDS;
        }

        public void Insert(CShopsProject model)
        {
            string strSQL;

            int ret; 

            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " INSERT INTO tbl_shopsproject (  ";
                strSQL = strSQL + "companyid ,";
                strSQL = strSQL + "projectname ,";
                strSQL = strSQL + "address ,";
                strSQL = strSQL + "developers ,";
                strSQL = strSQL + "startdate ,";
                strSQL = strSQL + "enddate ,";
                strSQL = strSQL + "totalarea ,";
                strSQL = strSQL + "buildarea ,";
                strSQL = strSQL + "projectbuildarea ) ";

                strSQL = strSQL + " VALUES (@companyid ,";
                strSQL = strSQL + "@projectname ,";
                strSQL = strSQL + "@address ,";
                strSQL = strSQL + "@developers ,";
                strSQL = strSQL + "@startdate ,";
                strSQL = strSQL + "@enddate ,";
                strSQL = strSQL + "@totalarea ,";
                strSQL = strSQL + "@buildarea ,";
                strSQL = strSQL + "@projectbuildarea ) ";

                


                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("companyid", model.companyid);
                clsDbTool.ParameterAdd("projectname", model.projectname);
                clsDbTool.ParameterAdd("address", model.address);
                clsDbTool.ParameterAdd("developers", model.developers);
                clsDbTool.ParameterAdd("startdate", model.startdate);
                clsDbTool.ParameterAdd("enddate", model.enddate);
                clsDbTool.ParameterAdd("totalarea", model.totalarea);
                clsDbTool.ParameterAdd("buildarea", model.buildarea);
                clsDbTool.ParameterAdd("projectbuildarea", model.projectbuildarea);

                //clsDbTool.SequenceText = "tbl_shopsproject";
                //clsDbTool.TableText = "tbl_shopsproject";
                clsDbTool.CommandText = strSQL;
                clsDbTool.Open();
                clsDbTool.BeginTransaction();
                clsDbTool.ExecuteScalar();

                clsDbTool.Commit();

            }
            catch (Exception ex)
            {
                clsDbTool.Rollback();
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }

           //return ret;
        }

        public void Update(CShopsProject model) 
        {
            string strSQL;


            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " UPDATE tbl_shopsproject SET   ";
                strSQL = strSQL + "companyid = @companyid,";
                strSQL = strSQL + "projectname = @projectname,";
                strSQL = strSQL + "address = @address,";
                strSQL = strSQL + "developers = @developers,";
                strSQL = strSQL + "startdate = @startdate,";
                strSQL = strSQL + "enddate = @enddate,";
                strSQL = strSQL + "totalarea = @totalarea,";
                strSQL = strSQL + "buildarea = @buildarea,";
                strSQL = strSQL + "projectbuildarea = @projectbuildarea";

                strSQL = strSQL + " WHERE 1=1";
                strSQL = strSQL + " AND projectid=@projectid";


                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("projectid", model.projectid);
                clsDbTool.ParameterAdd("companyid", model.companyid);
                clsDbTool.ParameterAdd("projectname", model.projectname);
                clsDbTool.ParameterAdd("address", model.address);
                clsDbTool.ParameterAdd("developers", model.developers);
                clsDbTool.ParameterAdd("startdate", model.startdate);
                clsDbTool.ParameterAdd("enddate", model.enddate);
                clsDbTool.ParameterAdd("totalarea", model.totalarea);
                clsDbTool.ParameterAdd("buildarea", model.buildarea);
                clsDbTool.ParameterAdd("projectbuildarea", model.projectbuildarea);

                clsDbTool.CommandText = strSQL;

                clsDbTool.Open();
                clsDbTool.BeginTransaction();

                clsDbTool.ExecuteScalar();

                clsDbTool.Commit();

            }
            catch (Exception ex)
            {
                clsDbTool.Rollback();
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }
        }

        public DataSet GetListByUpdate(string strId)
        {
            string strSQL;

            DataSet retDS = new DataSet();

            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " SELECT * ";
                strSQL = strSQL + " FROM tbl_ShopsProject ";
                strSQL = strSQL + " WHERE 1=1 ";

                if (!string.IsNullOrEmpty(strId))
                {
                    strSQL = strSQL + " AND projectid=@projectid ";
                }


                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("projectid", strId);
                clsDbTool.CommandText = strSQL;
                clsDbTool.Open();

                retDS = clsDbTool.GetDataSet();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }
            return retDS;
        }

        public DataSet GetAllList(string companyid) 
        {
            string strSQL;

            DataSet retDS = new DataSet();

            DataBaseTool clsDbTool = new DataBaseTool();

            try
            {
                strSQL = " SELECT * ";
                strSQL = strSQL + " FROM tbl_ShopsProject ";
                strSQL = strSQL + " WHERE 1=1 ";
                strSQL = strSQL + " AND companyid =@companyid ";
                


                clsDbTool.ParameterClear();
                clsDbTool.ParameterAdd("companyid", companyid);
                clsDbTool.CommandText = strSQL;
                clsDbTool.Open();

                retDS = clsDbTool.GetDataSet();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                clsDbTool.Close();
            }
            return retDS;
        }

    }
}
